<?php defined('SYSPATH') or die('No direct script access.');

class Report_DEC {

    private $columns = array();
    private $columns_properties = array();
    private $data = array();
    private $last_row = array();
    private $filters = array();
    private $actions = array();
    private $columns_manager = true;
    private $export_to_csv = true;
    private $export_to_pdf = true;
    private $edit_total_itens = true;
    private $show_pagination = true;
    private $cache = true;
    private $css = null;
    private $form_action = "";
    private $paging;
    private $query;
    private $order;
    private $direction;
    private $itens_per_page = 10;
    private $title;
    private $total_records;
    private $preffix;
    private $tokenizer;
    private $offset;

    public function __construct($sql, $itens_per_page=null, $preffix=null, $db=null) {
        if (!class_exists("Cache"))
            $this->cache = false;
        $this->query = trim($sql);
        if (isset($_POST['itens_per_page']) && !empty($_POST['itens_per_page'])) {
            $this->itens_per_page = intval($_POST['itens_per_page']);
        } elseif (null != $itens_per_page) {
            $this->itens_per_page = intval($itens_per_page);
        }
        if ($preffix) {
            $this->preffix = isset($_POST['preffix']) ? $_POST['preffix'] : $preffix;
        } else {
            $this->preffix = isset($_POST['preffix']) ? $_POST['preffix'] : uniqid();
        }
		$this->preffix = tokenizer::safe($this->preffix);
        $this->tokenizer = new tokenizer($this->query);
    }

    /**
     * set the form action, usefull to multiple report do not render the full page again
     *
     * @param string	$url		page url to send report ajax requests
     */
    public function set_form_action($url) {
        $this->form_action = $url;
    }

    /**
     * set the preffix (unique id), usefull to multiple report do not render the full page again
     *
     * @param string    $preffix    unique id
     */
    public function set_preffix($preffix) {
        $this->preffix = $preffix;
    }

    /**
     * enable/disable cache
     *
     * @param bool	$cache	
     */
    public function cacheable($cache) {
        $this->cache = ($cache && class_exists("Cache"));
    }

    /**
     * Adds an action to the specified column
     *
     * @param array		$optionName		name to use in the select->options
     * @param string	$function		javascript function used onChange behavior of the select
     */
    public function add_action_js($optionName, $function) {
        $this->actions[$function] = $optionName;
    }

    private function count_records($sql) {
		$distinct = (preg_match("/DISTINCT\s+([\`\_a-zA-Z\(\)0-9]+)(\.[\`\_a-zA-Z\(\)0-9]+)?(?=(,|FROM))/i", $sql, $distinct)) ? $distinct[0] : "(1)";
        $sql = preg_replace('/ORDER BY\s+([\`\_a-zA-Z\(\)0-9]+)(\.[\`\_a-zA-Z\(\)0-9]+)?(\s+DESC|\s+ASC)?(((\,)+(\s)+([\`\_a-zA-Z\(\)0-9]+)(\.[\`\_a-zA-Z\(\)0-9]+)?(\s+DESC|\s+ASC)?)+)?/i', '', $sql);
        if (!preg_match("/GROUP BY/", $sql)) {
            $sql = preg_replace("/SELECT(.)*FROM/i", "SELECT $distinct FROM", $sql, 1);
        }
		$sql = "SELECT COUNT(1) as ammount FROM ($sql) tmp";
        if (($result = cache::instance()->get($hash = md5($sql)))) {
            return $result;
        } else {
            cache::instance()->set($hash, $result = Database::instance()->query(Database::SELECT, $sql)->get("ammount"));
        }
        return $result;
    }

    public function parse($type="html", $views = null) {
        $this->query = $this->tokenizer->rebuild($this->filters);

        $this->total_records = $this->count_records($this->query);
        $this->paging = new Pagination(array('items_per_page' => $this->itens_per_page, 'total_items' => $this->total_records, 'preffix' => $this->preffix));

        // Set type render
        if (isset($_POST['render']) && !empty($_POST['render'])) {
            $type = tokenizer::safe($_POST['render']);
        }

		// Protect against parser injection
		if(!method_exists($this, "parse_" . $type)){
			$type = "html";	
		}
		
        if ("html" == $type) {
            if (!empty($this->offset)) {
                $this->offset = (int) (($this->paging->current_page - 1) * $this->paging->items_per_page) + $this->offset;
                $this->query = $this->tokenizer->set_limit($this->query, $this->itens_per_page, $this->offset);
            } else {
                $this->query = $this->tokenizer->set_limit($this->query, $this->itens_per_page, $this->paging->offset);
            }
        }
        if ($this->cache && $return = Cache::instance()->get($hash = md5($this->query . $type . serialize($views) . (Request::current()->is_ajax() ? 0 : 1)))) {
            if (Request::current()->is_ajax())
                die($return);
            return $return;
        }
        $results = Database::instance()->query(Database::SELECT, $this->query);
        foreach ($results as $row) {
            $this->data[] = $row;
        }
        if (isset($this->data[0])) {
            foreach ($this->data[0] as $key => $column) {
                $this->columns[$key]['label'] = $key;
            }
            foreach ($this->columns_properties as $column => $properties) {
                $this->columns[$column] = $properties;
            }
        }

        // Set filter event
        foreach ($this->filters as $column => $properties) {
            if (isset($properties["js_event"])) {
                if (!$this->ends_with($properties["js_function"], ")"))
                    $properties["js_function"] = $properties["js_function"] . "()";
                if (!$this->starts_with($properties["js_event"], "on"))
                    $properties["js_event"] = "on" . $properties["js_event"];
                $this->filters[$column]['event'] = $properties["js_event"] . "=\"" . $properties["js_function"] . ";\"";
            }
        }

        // Set order columns
        if (isset($_POST["order"])) {
			$orders = explode(",", $_POST['order']);
			$directions = explode(",",$_POST['direction']);
			$separator = "";
            $this->order = "";
            $this->direction = "";
			foreach($orders as $key => $value){
				$this->order .= $separator.$value;
				$this->direction .= $separator.$directions[$key];
				$this->columns[$value]["order"] = $directions[$key];
				$separator = ",";
			}
			unset($_POST['order']);
			unset($_POST['direction']);
        }

        // Set config columns
        foreach ($this->data as $key => $row) {
            foreach ($this->columns as $column => $properties) {
                if (isset($properties["function"])) {
                    $this->data[$key][$column] = call_user_func($properties["function"], $row[$column]);
                }
            }
        }

        // Set columns manager
        if (isset($_POST['column_manager']) && !empty($_POST['column_manager'])) {
            foreach ($this->columns as $column => $properties) {
                if (!in_array($column, $_POST['column_manager'])) {
                    $this->columns[$column]["display"] = false;
                    if (isset($this->last_row[$column])) {
                        unset($this->last_row[$column]);
                    }
                } else{
                    $this->columns[$column]["unselected"] = false;
				}
            }
        }

        foreach ($this->columns as $column => $properties) {
            if (!isset($properties["label"])) {
                $this->columns[$column]["label"] = $column;
            }
            if (!isset($properties["order"])) {
                $this->columns[$column]["order"] = "ASC";
            } else {
                $this->columns[$column]["order"] = ("ASC" == $this->columns[$column]["order"]) ? "DESC" : "ASC";
            }
            if (isset($properties["display"]) && $properties["display"] === false) {
                unset($this->columns[$column]);
                foreach ($this->data as $key => $row) {
                    unset($this->data[$key][$column]);
                    if (isset($this->last_row[$column])) {
                        unset($this->last_row[$column]);
                    }
                }
            }
            if (isset($properties["align"])) {
                foreach ($this->data as $key => $row) {
                    $this->data[$key][$column] = '<div style="text-align: ' . $properties["align"] . ';">' . $row[$column] . '</div>';
                }
            }
        }

        // Set Last Row
        foreach ($this->last_row as $column => $function) {
            $this->last_row[$column] = call_user_func($function, $column);
        }
        $return = call_user_func(array($this, "parse_" . $type), $views);
        if ($this->cache)
            cache::instance()->set($hash, $return);
        if (Request::current()->is_ajax())
            die($return);
        return $return;
    }

    private function parse_html($views = null) {
        if (is_null($views)) {
            $views = array("report" => "report/report.tpl", "middle" => "report/middle.tpl", "filter" => "report/filter.tpl", "pagination" => "report/pagination.tpl");
        }
        $array_columns = array("selected" => array(), "values" => array());
        foreach ($this->columns as $column => $properties) {
            if (!isset($properties["display"]) || $properties["display"] === true) {
				if(isset($properties["unselected"]) && true == $properties['unselected']){
					$properties["display"] = false;
					unset($this->columns[$column]);
					foreach ($this->data as $key => $row) {
						unset($this->data[$key][$column]);
						if (isset($this->last_row[$column])) {
							unset($this->last_row[$column]);
						}
					}
				} else{
					$array_columns["selected"][$column] = $column;
				}
                $array_columns["values"][$column] = $properties["label"];
            } else {
                $array_columns["values"][$column] = $properties["label"];
            }
        }
        $view = Smarty_View::factory($views["report"]);
        $view->form_action = $this->form_action;
        $view->preffix = $this->preffix;
        $view->title = $this->title;
        $view->middle = Smarty_View::factory($views["middle"]);
        $view->middle->preffix = $this->preffix;
        $view->middle->show_pagination = $this->show_pagination;
        if (true === $this->show_pagination)
            $view->middle->pagination = $this->paging->render($views["pagination"]);
        $view->middle->data = $this->data;
        $view->middle->columns = $this->columns;
        $view->middle->actions = $this->actions;
        $view->columns_values = $array_columns["values"];
        $view->columns_selected = $array_columns["selected"];
        $view->columns = $this->columns;
        $view->middle->last_row = $this->last_row;
        $view->middle->order = $this->order;
        $view->middle->direction = $this->direction;
        $view->export_to_csv = $this->export_to_csv;
        $view->export_to_pdf = $this->export_to_pdf;
        $view->columns_manager = $this->columns_manager;
        if (!empty($this->filters)) {
            $view->filter = Smarty_View::factory($views["filter"]);
            $view->filter->preffix = $this->preffix;
            $view->filter->filters = $this->filters;
        }
        if (Request::current()->is_ajax()) {
            return($view->middle);
        }
        return $view;
    }

    private function parse_pdf($views) {
        $header = array();
        $data = array();
        $align = array();
        foreach ($this->columns as $key => $column) {
            $header[$key] = $column['label'];
            if (isset($column['align'])) {
                if ('center' == $column['align']) {
                    $align[$key] = 'C';
                } elseif ('left' == $column['align']) {
                    $align[$key] = 'L';
                } elseif ('right' == $column['align']) {
                    $align[$key] = 'R';
                }
            } else {
                $align[$key] = 'C';
            }
        }
        foreach ($this->data as $fields) {
            $data[] = $fields;
        }
        $row_counter = sizeof($data);
        $column_counter = 0;
        foreach ($this->columns as $key => $column) {
            if (isset($this->last_row[$key])) {
                $data[$row_counter][$key] = $this->last_row[$key];
            } else {
                $data[$row_counter][$key] = "";
            }
        }
        $system_name = "";
        if (isset(Kohana::$config->load('dec')->sys_name)) {
            $system_name = Kohana::$config->load('dec')->sys_name;
        }
        $pdf_render = new Pdfrender();
        $pdf_render->render($header, $data, $align, $system_name, $this->title);
    }

    private function parse_csv($views) {
        $filename = "data_" . date("Y-m-d_H:i");
        header("Content-Type: text/csv; charset=ISO-8859-1");
        header('Content-Disposition: attachment; filename="' . $filename . '.csv"');
        header("Pragma: no-cache");
        header("Expires: 0");
        $csv = array();
        $row_counter = 0;
        $system_name = "";
        if (isset(Kohana::$config->load('dec')->sys_name)) {
            $system_name = Kohana::$config->load('dec')->sys_name;
        }
        $csv[$row_counter++][0] = $system_name;
        if ($this->title) {
            $csv[$row_counter++][0] = __("Report: ") . $this->title;
        }
        if (Model_User::logged_in()) {
            $user = Model_User::get_user();
            $user = $user->get_name();
        } else {
            $user = __("Visitor");
        }
        $csv[$row_counter++][0] = __("Requested By: ") . $user;
        $csv[$row_counter++][0] = __('Generated At: ') . date("Y-m-d H:i");
        if (!empty($_SERVER['HTTPS'])) {
            $csv[$row_counter++][0] = __("Report URL: ") . "https://" . $_SERVER['SERVER_NAME'] . $_SERVER['REQUEST_URI'];
        } else {
            $csv[$row_counter++][0] = __("Report URL: ") . "http://" . $_SERVER['SERVER_NAME'] . $_SERVER['REQUEST_URI'];
        }
        $csv[$row_counter++][0] = __("Report Total: ") . sizeof($this->data) . "\n";
        $column_counter = 0;
        foreach ($this->columns as $column) {
            $csv[$row_counter][$column_counter++] = $column['label'];
        }
        $row_counter++;
        foreach ($this->data as $fields) {
            $csv[$row_counter++] = $fields;
        }
        $column_counter = 0;
        if (sizeof($this->last_row)) {
            foreach ($this->columns as $key => $column) {
                if (isset($this->last_row[$key])) {
                    $csv[$row_counter][$column_counter++] = $this->last_row[$key];
                } else {
                    $csv[$row_counter][$column_counter++] = "";
                }
            }
        }
        $result = "";
        foreach ($csv as $row) {
            $result .= implode(";", $row) . "\n";
        }
        die($result);
    }

    public function set_title($title) {
        $this->title = $title;
    }

    public function set_itens_per_page($itens_per_page) {
        $this->itens_per_page = $itens_per_page;
    }

    public function disable_export_to_csv() {
        $this->exportToCSV = false;
    }

    public function disable_export_to_pdf() {
        $this->export_to_pdf = false;
    }

    public function disable_columns_manager() {
        $this->columns_manager = false;
    }

    public function disable_edit_total_itens() {
        $this->edit_total_itens = false;
    }

    public function set_offset($offset) {
        $this->offset = $offset;
    }

    public function get_preffixe() {
        return $this->preffix;
    }

    public function multi_array_key_exists($mult_array, $array) {
        foreach ($mult_array as $key => $value) {
            if (array_key_exists($key, $array) || array_key_exists($key . '_start', $array)) {
                return $key;
            }
        }
        return false;
    }

    /**
     * Set a custom CSS file for use in the grid
     *
     * @param string $fileName	name of the CSS file
     */
    public function set_css($file) {
        $this->css = $file;
    }

    /**
     * Set bindings for a given column
     *
     * @param	string		$column				the original name of the column
     * @param	string		$display			the name to display
     * @param	string		$function			function to format the value
     */
    public function bind($column, $label, $function=null) {
        $this->columns_properties[$column]["label"] = $label;
        if (null != $function) {
            $this->columns_properties[$column]["function"] = $function;
        }
    }

    public function hide($column) {
        $this->columns_properties[$column]["display"] = false;
    }

    /**
     * Unselect a displayed column for default, but mantains on columns manager to show, if user select it
     *
     * @param	string		$column				the original name of the column
     */
    public function column_manager_unselect($column) {
        $this->columns_properties[$column]["unselected"] = true;
    }
	
    /**
     * Set the horizontal column alignment (left, center or right)
     *
     * @param	string		$column				the original name of the column
     * @param	boolean		$align				the horizontal alignment (left, center or right)
     */
    public function align($column, $align) {
        $this->columns_properties[$column]["align"] = $align;
    }

    public function format_name($name) {
        return ucwords(strtolower($name));
    }

    public function translate($string) {
        return __(ucfirst($string));
    }

    public function __toString() {
        try {
            return $this->parse()->render();
        } catch (Exception $e) {
            print_r($e);
        }
    }

    /**
     * Set the last row of column value
     * 
     * @param	string		$column					the original name of the column
     * @param	string		$lastRowFunction		function to retrieve the value
     */
    public function set_last_row($column, $last_row_function) {
        $this->last_row[$column] = $last_row_function;
    }

    /**
     * Add an input filter
     *
     * @param string 	$column				filter input name
     * @param string 	$display			text to identify the input
     * @param string 	$type				types: text (default), date, select, select-multiple, radio, radio-inline, checkbox, checkbox-inline
     * @param boolean 	$catch				true if the Report must manage automatically the filter
     */
    public function add_filter($column, $display, $type="text", $values="", $catch=true) {
        if ("select" == $type) {
            $temp = array("" => "");
            foreach ($values as $key => $value) {
                $temp[$key] = $value;
            }
            $values = $temp;
        }
        $properties = array();
        $properties["display"] = $display;
        $properties["type"] = $type;
        $properties["values"] = $values;
        $properties["catch"] = $catch;
        $this->filters[$column] = $properties;
        if ("date" != $type) {
            $event = ("text" == $type || "numeric" == $type || "numeric-range" == $type) ? "onChange" : "onClick";
            $this->add_filter_event($column, $event, "report_refresh('" . $this->get_preffixe() . "')");
        }
    }

    /**
     * Adds an event for a filter
     *
     * @param string	$column		input name to receive the event
     * @param string	$event		Javascript event to catch
     * @param string	$function	Javascript function to be called on event fire
     */
    public function add_filter_event($column, $event, $function) {
        $this->filters[$column]["js_event"] = $event;
        $this->filters[$column]["js_function"] = $function;
    }

    /**
     * Sum columns
     *
     * @param	array	$array	array multi-dimensional
     * @param	String	$column	column name by sum
     * @return	numeric
     */
    public function sum($column) {
        $result = 0;
        foreach ($this->data as $key => $row) {
            $result += $row[$column];
        }
        return $result;
    }

    /**
     * Simple Arithmetic Average
     *
     * @param	array	$array	array multi-dimensional
     * @param	String	$column	column name by sum
     * @return	numeric
     */
    public function simple_arithmetic_average($column) {
        $result = 0;
        if (sizeof($this->data) == 0)
            return 0;
        foreach ($this->data as $key => $row) {
            $result += $row[$column];
        }
        return $result / sizeof($this->data);
    }

    public function get_total_records() {
        return $this->total_records;
    }

    /**
     * Weighted Average
     */
    public function weighted_average() {
        return false;
    }

    /**
     * Standard Deviation
     */
    public function standard_deviation() {
        return false;
    }

    /**
     * Test if the string starts with a required substring
     * 
     * @param	string	$subject	the whole string
     * @param	string	$needle		the required substring
     * @param	boolean	$sensitive	true for case sensitive check
     * @return	boolean
     */
    function starts_with($subject, $needle, $sensitive=false) {
        if ($sensitive)
            return 0 === strpos($subject, $needle);
        return 0 === stripos($subject, $needle);
    }

    /**
     * Test if the string ends with a required substring
     * 
     * @param	string	$subject	the whole string
     * @param	string	$needle		the required substring
     * @param	boolean	$sensitive	true for case sensitive check
     * @return	boolean
     */
    function ends_with($stack, $needle, $sensitive=false) {
        if ($sensitive)
            return $needle == substr($stack, strripos($stack, $needle));
        return $needle == substr($stack, strrpos($stack, $needle));
    }

    /**
     * Disable pagination
     */
    function disable_pagination() {
        $this->show_pagination = false;
    }

} // End Dec Report